﻿Imports System.Data
Imports System.Xml
Imports System.Data.SqlClient
Imports DevComponents.DotNetBar
Imports DoAnQuanLyBanHang1065043_1065007.DoAnQuanLyBanHang.General
Imports System.Windows.Forms

Namespace DoAnQuanLyBanHang.General
    Partial Class DataService
        Inherits DataTable
#Region "Cac bien"
        Private Shared m_Connection As SqlConnection
        Public Shared m_ConnectString As [String] = My.MySettings.[Default].ConnectionString
        Private m_Command As SqlCommand
        Private m_DataAdapter As SqlDataAdapter
#End Region


        Public Sub New()
        End Sub
#Region "Ket noi he quan tri co so du lieu"
        Public Shared Sub ConnectDBMS()
        End Sub
#End Region
#Region "Ket noi du lieu"
        Public Shared Sub ConnectionString()
            Dim XmlDOcument As XmlDocument = Xml.ReadXml("")
            Dim XmlElement As XmlElement = XmlDOcument.DocumentElement

            Try
                If XmlElement.SelectSingleNode("costatus").InnerText = "true" Then
                    m_ConnectString = "Data Source=" & XmlElement.SelectSingleNode("servername").InnerText & ";Initial Catalog=" & Convert.ToString(XmlElement.SelectSingleNode(";Integrated Security=True;"))
                Else
                    m_ConnectString = "Data Source=" & XmlElement.SelectSingleNode("servername").InnerText & ";Initial Catalog=" & XmlElement.SelectSingleNode("database").InnerText & ";User Id=" & XmlElement.SelectSingleNode("username").InnerText & ";Password=" & XmlElement.SelectSingleNode("password").InnerText & ";"
                End If
                ThamSo.DatabaseName = XmlElement.SelectSingleNode("database").InnerText
            Catch
                MessageBoxEx.Show("Không thể kết nối được cơ sở dữ liệu! " & vbLf & " Lỗi: kết nối", "Lỗi", MessageBoxButtons.OK, MessageBoxIcon.[Error])
            End Try

        End Sub
#End Region


#Region "dua du lieu"
        Public Overloads Sub Load(m_Sql As SqlCommand)
            m_Command = m_Sql
            Try
                m_Command.Connection = m_Connection

                m_DataAdapter = New SqlDataAdapter()
                m_DataAdapter.SelectCommand = m_Command

                Me.Clear()
                m_DataAdapter.Fill(Me)
            Catch e As Exception
                MessageBoxEx.Show("Không thể thực thi câu lệnh SQL này!" & vbLf & "Lỗi: " & e.Message, "Lỗi", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.[Error])
            End Try
        End Sub
#End Region

#Region "mo ket noi"
        Public Shared Function OpenConnection() As Boolean
            Try
                If m_Connection Is Nothing Then
                    m_Connection = New SqlConnection(m_ConnectString)
                End If
                If m_Connection.State = ConnectionState.Closed Then
                    m_Connection.Open()
                End If
                Return True
            Catch
                m_Connection.Close()
                Return False
            End Try
        End Function
#End Region

#Region "dong ket noi"
        Public Sub CloseConnection()
            m_Connection.Close()
        End Sub
#End Region

#Region "Cap nhat du lieu"
        Public Function ExecuteNoneQuery() As Integer
            Dim result As Integer = 0
            Dim m_SqlTran As SqlTransaction = Nothing
            Try
                m_SqlTran = m_Connection.BeginTransaction()

                m_Command.Connection = m_Connection
                m_Command.Transaction = m_SqlTran

                m_DataAdapter = New SqlDataAdapter()
                m_DataAdapter.SelectCommand = m_Command

                Dim builder As New SqlCommandBuilder(m_DataAdapter)

                result = m_DataAdapter.Update(Me)
                m_SqlTran.Commit()
            Catch
                If m_SqlTran IsNot Nothing Then
                    m_SqlTran.Rollback()
                End If
                Throw
            End Try
            Return result
        End Function
#End Region

#Region "Thực thi câu lệnh SQL"
        Public Function ExecuteNoneQuery(m_Sql As SqlCommand) As Integer
            Dim result As Integer = 0
            Dim m_SqlTran As SqlTransaction = Nothing
            Try
                m_SqlTran = m_Connection.BeginTransaction()

                m_Sql.Connection = m_Connection
                m_Sql.Transaction = m_SqlTran
                result = m_Sql.ExecuteNonQuery()

                Me.AcceptChanges()
                m_SqlTran.Commit()
            Catch
                If m_SqlTran IsNot Nothing Then
                    m_SqlTran.Rollback()
                End If
                Throw
            End Try
            Return result
        End Function
#End Region

#Region "ExecuteScalar"
        Public Function ExecuteScalar(m_Sql As SqlCommand) As Object
            Dim result As Object = Nothing
            Dim m_SqlTran As SqlTransaction = Nothing
            Try
                m_SqlTran = m_Connection.BeginTransaction()
                m_Sql.Connection = m_Connection
                m_Sql.Transaction = m_SqlTran
                result = m_Sql.ExecuteScalar()

                Me.AcceptChanges()
                m_SqlTran.Commit()
                If result Is DBNull.Value Then
                    result = Nothing
                End If
            Catch
                If m_SqlTran IsNot Nothing Then
                    m_SqlTran.Rollback()
                End If
                Throw
            End Try
            Return result
        End Function
#End Region

#Region "Doi mat khau"
        Public Sub DoiMatKhau(userName As [String], newPassword As [String])
            m_DataAdapter = New SqlDataAdapter()

            Dim cmd As New SqlCommand("UPDATE Phan_Quyen SET MatKhau = @matkhau WHERE TenDangNhap = @tendangnhap")
            cmd.Parameters.Add("tendangnhap", SqlDbType.VarChar).Value = userName
            cmd.Parameters.Add("matkhau", SqlDbType.VarChar).Value = newPassword

            If m_ConnectString = "" Then
                ConnectionString()
            End If

            If m_Connection Is Nothing OrElse m_Connection.State = ConnectionState.Closed Then
                m_Connection = New SqlConnection(m_ConnectString)
                m_Connection.Open()
            End If

            m_Command = New SqlCommand()
            m_Command = cmd

            Try
                m_Command.Connection = m_Connection

                m_DataAdapter = New SqlDataAdapter()
                m_DataAdapter.SelectCommand = m_Command

                'this.Clear();

                m_DataAdapter.Fill(Me)

                Dim buider As New SqlCommandBuilder(m_DataAdapter)
                m_DataAdapter.Update(Me)
            Catch e As Exception
                MessageBoxEx.Show("Không thể thực thi câu lệnh SQL này!" & vbLf & "Lỗi: " & e.Message, "Lỗi", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.[Error])
            End Try
        End Sub
#End Region

    End Class
End Namespace


